package com.wframe.project.jnd.dao;

import com.alibaba.fastjson.JSON;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.frame.util.DateConvert;
import com.wframe.framework.database.BaseDao;
import com.wframe.project.former.entity.ProductsAttrvalBean;
import com.wframe.project.jnd.entity.BuySendPromotion;
import com.wframe.project.jnd.entity.FlashSalePromotion;
import com.wframe.project.jnd.entity.Promotion;
import com.wframe.project.jnd.entity.PurchaseGiftItem;
import com.wframe.project.jnd.util.TransformUtils;
import com.wframe.project.jnd.vo.ActiveAreaProduct;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * Created by lwb on 2017/6/3.
 */
@Repository
public class PromotionDao extends BaseDao {
    public Map<String, String> findAll(String priceid) {
        StringBuilder sb = new StringBuilder("SELECT id,case promotion_type");
        for (Promotion.Type type : Promotion.Type.values()) {
            sb.append(" WHEN '").append(type.ordinal()).append("' then '").append(type.name()).append("'");
        }
        sb.append(" end type from wb_promotions WHERE platform in (").append(Promotion.Platform.COMMON.ordinal()).append(",").append(Promotion.Platform.PC.ordinal()).append(") and start_time < ? and end_time > ? and state=1 and "
                + " (product_type=0 or (product_type=1 and product_val=?)"
                + " or (product_type=2 and product_val=(SELECT promotionid from wb_promotion_product WHERE productid=?))"
                + " or (product_type=3 and product_val=(SELECT classid from wb_products_and_class WHERE productid=?))) GROUP BY weight ORDER BY second_priority desc");
        String now = DateConvert.toDefaultString(new Date());
        List<Map> list = mapList(sb.toString(), new Object[]{now, now, priceid, priceid, priceid});
        Map<String, String> result = new TreeMap<>();
        for (Map map : list) {
            result.put(String.valueOf(map.get("type")), String.valueOf(map.get("id")));
        }
        return result;
    }

    public Promotion findFlashSale(String promotionId) throws Exception {
        JdataBean flashSale = findFlashSaleVo(promotionId);
        if (Objects.isNull(flashSale)) {
            return null;
        }
        return TransformUtils.trans(flashSale, FlashSalePromotion.class);
    }

    public Promotion findBuySend(String promotionId) throws Exception {
        return TransformUtils.trans(findBuySendVO(promotionId), BuySendPromotion.class);
    }

    public List<JdataBean> findMoneyOff(String promotionId) {
        String sql = "SELECT m.id itemId,m.off_money,m.discount_money,m.discount_rate,m.promotionid id,n.time,n.description from wb_promotion_money_off m"
                + " JOIN wb_promotions n on m.promotionid=n.id WHERE m.promotionid=? order by m.off_money desc";
        return list(sql, promotionId);
    }

    public List<PurchaseGiftItem> findPurchaseGift(String promotionId) {
        String sql = "SELECT ppg.id,ppg.min_money minMoney,ppg.min_number minNumber,GROUP_CONCAT(ppgp.giftid) giftIds,GROUP_CONCAT(ppgp.productid) productIds from wb_promotion_purchase_gift ppg " +
                " JOIN wb_promotion_purchase_gift_product ppgp on ppg.id=ppgp.giftid " +
                " join wb_products prod on ppgp.productid=prod.id " +
                " WHERE ppg.promotionid=? and prod.`status`=2 " +
                " GROUP BY ppg.min_money,ppg.min_number " +
                " order by min_money desc,min_number desc ";
        return list(sql, promotionId, PurchaseGiftItem.class);
    }

    public List<JdataBean> findGifts(String giftIds, String productIds) {
        String sql = "select t.productid,IFNULL(s.viewpath,sm.viewpath) photo,m.giftid,m.unit,p.name"
                + " from wb_promotion_purchase_gift_product m"
                + " join wb_products_prices t on m.productid=t.productid"
                + " join wb_products p on p.id=t.productid"
                + " left join app_pictures s on s.id=t.photo"
                + " left join app_pictures sm on sm.id=p.photo"
                + " WHERE m.giftid in (" + giftIds + ") and m.productid in (" + productIds + ")";
        return list(sql, null);
    }

    public JdataBean findFlashSaleVo(String id) {
        String sql = "SELECT m.id as fsid,m.count_down,m.price,m.amount,n.id,UNIX_TIMESTAMP(n.end_time) endDate,UNIX_TIMESTAMP() now,n.name,n.time,n.description from wb_promotion_flash_sale m"
                + " join wb_promotions n on m.promotionid=n.id WHERE m.promotionid=? and m.amount>0";
        return uniqueResult(sql, id);
    }

    public JdataBean findBuySendVO(String promotionId) {
        String sql = "SELECT m.promotionid id,m.buy_number,m.send_number,n.time,n.description from wb_promotion_buy_send m JOIN wb_promotions n on m.promotionid=n.id WHERE m.promotionid=?";
        return uniqueResult(sql, promotionId);
    }

    public JdataBean findHistory(String userid, String promotionid) {
        String sql = "SELECT ifnull(sum(m.times),0) history,n.time from wb_promotion_histories m JOIN wb_promotions n on m.promotionid=n.id WHERE m.userid=? and n.id=?";
        return uniqueResult(sql, new Object[]{userid, promotionid});
    }

    public boolean isUsable(String userid, String promotionid) {
        String sql = "SELECT EXISTS (SELECT 1 from wb_promotion_controlsale m"
                + "  where m.promotion_id=? and (m.type=1 and m.val=(SELECT usergroup from wbuser WHERE id=?)"
                + " or (m.type=2 and m.val=(SELECT pricegrade from wbuser WHERE id=?))"
                + " or (m.type=3 and m.val in(SELECT id from city WHERE name in (SELECT province from wb_company WHERE userid=? union SELECT city from wb_company WHERE userid=? union SELECT region from wb_company WHERE userid=?)))))";
        int isUsable = Integer.parseInt(String.valueOf(uniqueColumnResult(sql, new Object[]{promotionid, userid, userid, userid, userid, userid})));
        return isUsable == 1;
    }

    public JdataBean findFlashSaleByProductId(String productId, String userId, int platform) {
        String sql = "SELECT " +
                " prom.id, " +
                " prom.product_val productid,if(prom.time is not null,prom.time-ifnull(wph.time,0),null)times, " +
                " pfs.price, " +
                "IF ( " +
                " pfs.amount > ( " +
                "  IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) " +
                " ), " +
                " ( " +
                "  IFNULL(wpp.amount, 0) - IFNULL(wpp.djamount, 0) " +
                " ), " +
                " pfs.amount " +
                ") amount " +
                "FROM " +
                " wb_promotion_flash_sale pfs " +
                "JOIN wb_promotions prom ON pfs.promotionid = prom.id " +
                "JOIN wb_products_prices wpp ON prom.product_val = wpp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  SUM(IFNULL(wph.times, 0)) time, " +
                "  wph.promotionid " +
                " FROM " +
                "  wb_promotion_histories wph " +
                " WHERE " +
                "  wph.userid = ? " +
                " GROUP BY " +
                "  promotionid " +
                ") wph ON prom.id = wph.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.promotion_id, " +
                "  wpc.id " +
                " FROM " +
                "  wb_promotion_controlsale wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   wc.province, " +
                "   wc.city, " +
                "   wc.region " +
                "  FROM " +
                "   wb_company wc " +
                "  JOIN wbuser wu ON wc.userid = wu.id " +
                "  WHERE " +
                "   wc.state = 2 " +
                "  AND wu.state = 1 " +
                "  AND wu.authstate = 2 " +
                "  AND wu.id = ? " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                " GROUP BY " +
                "  wpc.promotion_id " +
                ") wpc ON prom.id = wpc.promotion_id " +
                "WHERE " +
                " pfs.amount > 0 " +
                "AND prom.promotion_type = 0 " +
                "AND prom.product_type = 1 " +
                "AND prom.product_val = ? " +
                "AND prom.weight = 0 " +
                "AND prom.second_priority = 1 " +
                "AND prom.platform IN (0," + platform + ") " +
                "AND NOW() BETWEEN prom.start_time " +
                "AND prom.end_time " +
                "AND prom.state = 1 " +
                "AND ( " +
                " prom.time IS NULL " +
                " OR prom.time > IFNULL(wph.time, 0) " +
                ") " +
                "AND ( " +
                " prom.is_control = 0 " +
                " OR ( " +
                "  prom.is_control = 1 " +
                "  AND wpc.id IS NOT NULL " +
                " ) " +
                ")";
        return uniqueResult(sql, new Object[]{userId, userId, productId});
    }

    public JdataBean findBuySendByProductId(String productId, String userId) {
        String sql = "SELECT  " +
                " m.promotionid id,  " +
                " m.buy_number,  " +
                " m.send_number,  " +
                " pm.time  " +
                "FROM  " +
                " wb_promotion_buy_send m  " +
                "JOIN wb_promotions pm ON m.promotionid = pm.id  " +
                "LEFT JOIN wb_promotion_controlsale wpc ON pm.id = wpc.promotion_id  " +
                "INNER JOIN (  " +
                " SELECT  " +
                "  uu.ID AS u_id,  " +
                "  uu.UserGroup AS u_UserGroup,  " +
                "  uu.pricegrade AS u_pricegrade,  " +
                "  IFNULL(wc.province, '') AS u_province,  " +
                "  IFNULL(wc.city, '') AS u_city,  " +
                "  IFNULL(wc.region, '') AS u_region  " +
                " FROM  " +
                "  wbuser uu  " +
                " LEFT JOIN wb_company wc ON uu.ID = wc.userid  " +
                " WHERE  " +
                "  uu.ID = ?  " +
                ") wu  " +
                "LEFT JOIN wb_area_trad wat ON (  " +
                " wpc.type = 3  " +
                " AND wpc.val = wat.id  " +
                ")  " +
                "WHERE  " +
                " pm.promotion_type = 1  " +
                "AND pm.product_type = 1  " +
                "AND pm.product_val = ?  " +
                "AND pm.weight = 1  " +
                "AND pm.second_priority = 1  " +
                "AND pm.platform IN (0, 1)  " +
                "AND pm.start_time < ?  " +
                "AND pm.end_time > ?  " +
                "AND pm.state = 1  " +
                "AND (  " +
                " pm.is_control = 0  " +
                " OR (  " +
                "  pm.is_control = 1  " +
                "  AND (  " +
                "   (  " +
                "    wpc.type = 1  " +
                "    AND wpc.val = u_UserGroup  " +
                "   )  " +
                "   OR (  " +
                "    wpc.type = 2  " +
                "    AND wpc.val = u_pricegrade  " +
                "   )  " +
                "   OR (  " +
                "    wpc.type = 3  " +
                "    AND (  " +
                "     IFNULL(u_province, '') != ''  " +
                "     AND wat.province LIKE CONCAT(u_province, '%')  " +
                "     AND IFNULL(wat.city, '') = ''  " +
                "     AND IFNULL(wat.area, '') = ''  " +
                "    )  " +
                "    OR (  " +
                "     IFNULL(u_province, '') != ''  " +
                "     AND wat.province LIKE CONCAT(u_province, '%')  " +
                "     AND IFNULL(u_city, '') != ''  " +
                "     AND wat.city LIKE CONCAT(u_city, '%')  " +
                "     AND IFNULL(wat.area, '') = ''  " +
                "    )  " +
                "    OR (  " +
                "     IFNULL(u_province, '') != ''  " +
                "     AND wat.province LIKE CONCAT(u_province, '%')  " +
                "     AND IFNULL(u_city, '') != ''  " +
                "     AND wat.city LIKE CONCAT(u_city, '%')  " +
                "     AND IFNULL(u_region, '') != ''  " +
                "     AND wat.area LIKE CONCAT(u_region, '%')  " +
                "    )  " +
                "   )  " +
                "  )  " +
                " )  " +
                ")";

        String now = DateConvert.toDefaultString(new Date());
        return uniqueResult(sql, new Object[]{userId, productId, now, now});
    }

    public boolean isControlSale(String promotionid) {
        String sql = "SELECT is_control from wb_promotions WHERE id=?";
        int isControlSale = Integer.parseInt(String.valueOf(uniqueColumnResult(sql, promotionid)));
        return isControlSale == 0;
    }

    public boolean isFlashSale(String productId) {
        String sql = "SELECT EXISTS (SELECT 1 from wb_promotions m JOIN wb_promotion_flash_sale n on m.id=n.promotionid WHERE promotion_type=" + Promotion.Type.FLASH_SALE.ordinal() + " and state=1 and platform in (0,1) and start_time < ? and end_time > ? and product_type=1 and product_val=? and amount > 0)";
        String now = DateConvert.toDefaultString(new Date());
        int isFlashSale = Integer.parseInt(String.valueOf(uniqueColumnResult(sql, new Object[]{now, now, productId})));
        return isFlashSale == 1;
    }

    public boolean isBuySend(String productId) {
        String sql = "SELECT EXISTS (SELECT 1 from wb_promotions WHERE promotion_type=1 and state=1 and platform in (0,1) and start_time < ? and end_time > ? and product_type=1 and product_val=?)";
        String now = DateConvert.toDefaultString(new Date());
        int isBuySend = Integer.parseInt(String.valueOf(uniqueColumnResult(sql, new Object[]{now, now, productId})));
        return isBuySend == 1;
    }

    public PageResult findOnFlashSaleByPlatform(int platform, Long uid, int pagesize, int pageno) {
        String sql = null;
        Object[] params = null;
        if (uid == null) {
            sql = " SELECT " +
                    " pm.id AS pm_id, " +
                    " pm.start_time AS pm_start_time, " +
                    " pm.end_time AS pm_end_time, " +
                    " CAST( ( " +
                    "  UNIX_TIMESTAMP(IFNULL(pm.end_time, NOW())) - UNIX_TIMESTAMP(NOW()) " +
                    " ) AS DECIMAL ) AS pm_surplus_seconds, " +
                    " pm.`name` AS pm_name, " +
                    " pm.description AS pm_description, " +
                    " pm.promotion_type AS pm_promotion_type, " +
                    " bs.buy_number AS bs_buy_number, " +
                    " bs.send_number AS send_number, " +
                    " fs.id AS fs_id, " +
                    " fs.`name` AS fs_name, " +
                    " fs.price AS fs_price, " +
                    " IF ( " +
                    "  fs.amount <= (pr.amount - pr.djamount), " +
                    "  CAST( fs.amount AS DECIMAL ) , " +
                    "  CAST( (pr.amount - pr.djamount) AS DECIMAL ) " +
                    " ) AS fs_amount, " +
                    " CAST( fs.amount AS DECIMAL ) AS fs_limit_amount, " +
                    " CAST( fs.sold_amount AS DECIMAL ) AS fs_sold_amount, " +
                    " CAST( fs.count_down AS DECIMAL ) AS fs_count_down, " +
                    " pp.ID AS pp_id, " +
                    " pp.`name` AS pp_name, " +
                    " pp.`code` AS pp_code, " +
                    " pp.photo AS pp_photo, " +
                    " pp.unitname AS pp_unitname, " +
                    " pp.attrvals AS pp_attrvals, " +
                    " pr.price AS pr_price, " +
                    " pic.filepath AS pic_filepath, " +
                    " pic.viewpath AS pic_photoview " +
                    " FROM " +
                    " wb_promotions pm " +
                    " LEFT JOIN wb_promotion_flash_sale fs ON pm.id = fs.promotionid " +
                    " LEFT JOIN wb_promotion_buy_send bs ON pm.id = bs.promotionid " +
                    " RIGHT JOIN wb_products pp ON pm.product_val = pp.ID " +
                    " LEFT JOIN wb_products_prices pr ON pr.productid = pp.ID " +
                    " LEFT JOIN app_pictures pic ON pp.photo = pic.id " +
                    " WHERE " +
                    " (pm.promotion_type = 0 or pm.promotion_type =1) " +
                    " AND pm.show_sell = 1 " +
                    " AND pm.start_time <= NOW() " +
                    " AND pm.end_time > NOW() " +
                    " AND pm.state = 1 " +
                    " AND ( pm.platform = 0 OR pm.platform = ? ) " +
                    " AND pm.product_type = 1 " +
                    " AND pp. STATUS = 2 " +
                    " ORDER BY " +
                    " pm.sort_value DESC, " +
                    " pm.weight DESC, " +
                    " pm.second_priority DESC, " +
                    " pm.start_time ASC, " +
                    " pm.end_time ASC, " +
                    " pp.orderno DESC ";
            params = new Object[]{platform};
        } else {
            sql = " SELECT " +
                    " pm.id AS pm_id, " +
                    " pm.start_time AS pm_start_time, " +
                    " pm.end_time AS pm_end_time, " +
                    " CAST( " +
                    "  ( " +
                    "   UNIX_TIMESTAMP(IFNULL(pm.end_time, NOW())) - UNIX_TIMESTAMP(NOW()) " +
                    "  ) AS DECIMAL " +
                    " ) AS pm_surplus_seconds, " +
                    " pm.`name` AS pm_name, " +
                    " pm.description AS pm_description, " +
                    " pm.promotion_type AS pm_promotion_type, " +
                    " bs.buy_number AS bs_buy_number, " +
                    " bs.send_number AS send_number, " +
                    " fs.id AS fs_id, " +
                    " fs.`name` AS fs_name, " +
                    " fs.price AS fs_price, " +
                    " " +
                    " IF ( " +
                    " fs.amount <= (pr.amount - pr.djamount), " +
                    " CAST(fs.amount AS DECIMAL), " +
                    " CAST( " +
                    "  (pr.amount - pr.djamount) AS DECIMAL " +
                    " ) " +
                    " ) AS fs_amount, " +
                    " CAST(fs.amount AS DECIMAL) AS fs_limit_amount, " +
                    " CAST(fs.sold_amount AS DECIMAL) AS fs_sold_amount, " +
                    " CAST(fs.count_down AS DECIMAL) AS fs_count_down, " +
                    " pp.ID AS pp_id, " +
                    " pp.`name` AS pp_name, " +
                    " pp.`code` AS pp_code, " +
                    " pp.photo AS pp_photo, " +
                    " pp.unitname AS pp_unitname, " +
                    " pp.attrvals AS pp_attrvals, " +
                    " ppg.price AS pr_price, " +
                    " pic.filepath AS pic_filepath, " +
                    " pic.viewpath AS pic_photoview, " +
                    " " +
                    " IF ( " +
                    " pm.is_control = 0 " +
                    " OR pcs.pcs_id IS NOT NULL, " +
                    " TRUE, " +
                    " FALSE " +
                    " ) AS can_promotion " +
                    " FROM " +
                    " wb_promotions pm " +
                    " LEFT JOIN wb_promotion_flash_sale fs ON pm.id = fs.promotionid " +
                    " LEFT JOIN wb_promotion_buy_send bs ON pm.id = bs.promotionid " +
                    " RIGHT JOIN wb_products pp ON pm.product_val = pp.ID " +
                    " LEFT JOIN ( " +
                    " SELECT " +
                    "  wppg.productid, " +
                    "  wppg.price " +
                    " FROM " +
                    "  wb_products_pricegrade wppg " +
                    " INNER JOIN wbuser wu ON wppg.grade = wu.pricegrade " +
                    " WHERE " +
                    "  wu.ID = ? " +
                    " ) ppg ON ppg.productid = pp.ID " +
                    " LEFT JOIN wb_products_prices pr ON pr.productid = pp.ID " +
                    " LEFT JOIN ( " +
                    " SELECT " +
                    "  wpc.id AS pcs_id, " +
                    "  wpc.promotion_id AS pcs_promotion_id " +
                    " FROM " +
                    "  wb_promotion_controlsale wpc " +
                    " INNER JOIN ( " +
                    "  SELECT " +
                    "   uu.ID AS u_id, " +
                    "   uu.UserGroup AS u_UserGroup, " +
                    "   uu.pricegrade AS u_pricegrade, " +
                    "   IFNULL(wc.province, '') AS u_province, " +
                    "   IFNULL(wc.city, '') AS u_city, " +
                    "   IFNULL(wc.region, '') AS u_region " +
                    "  FROM " +
                    "   wbuser uu " +
                    "  LEFT JOIN wb_company wc ON uu.ID = wc.userid " +
                    "  WHERE " +
                    "   uu.ID = ? " +
                    " ) wu " +
                    " LEFT JOIN wb_area_trad wat ON ( " +
                    "  wpc.type = 3 " +
                    "  AND wpc.val = wat.id " +
                    " ) " +
                    " WHERE " +
                    "  (type = 1 AND val = u_UserGroup) " +
                    " OR (type = 2 AND val = u_pricegrade) " +
                    " OR ( " +
                    "  type = 3 " +
                    "  AND ( " +

                    "   ( " +
                    "        IFNULL(u_province,'') != '' " +
                    "    AND wat.province LIKE CONCAT(u_province, '%') " +
                    "    AND IFNULL(wat.city, '') = '' " +
                    "    AND IFNULL(wat.area, '') = '' " +
                    "   ) " +
                    "   OR ( " +
                    "        IFNULL(u_province,'') != '' " +
                    "    AND wat.province LIKE CONCAT(u_province, '%') " +
                    "        AND IFNULL(u_city,'') != '' " +
                    "    AND wat.city LIKE CONCAT(u_city, '%') " +
                    "    AND IFNULL(wat.area, '') = '' " +
                    "   ) " +
                    "   OR ( " +
                    "    IFNULL(u_province,'') != '' " +
                    "    AND wat.province LIKE CONCAT(u_province, '%') " +
                    "        AND IFNULL(u_city,'') != '' " +
                    "    AND wat.city LIKE CONCAT(u_city, '%') " +
                    "    AND IFNULL(u_region,'') != '' " +
                    "    AND wat.area LIKE CONCAT(u_region, '%') " +
                    "   ) " +

                    "  ) " +
                    " ) " +
                    " GROUP BY " +
                    "  wpc.promotion_id " +
                    " ) pcs ON pm.is_control = 1 " +
                    " AND pcs.pcs_promotion_id = pm.id " +
                    " LEFT JOIN app_pictures pic ON pp.photo = pic.id " +
                    " WHERE " +
                    " (pm.promotion_type=0 or pm.promotion_type =1 ) " +
                    " AND pm.show_sell = 1 " +
                    " AND pm.start_time <= NOW() " +
                    " AND pm.end_time > NOW() " +
                    " AND pm.state = 1 " +
                    " AND ( " +
                    " pm.platform = 0 " +
                    " OR pm.platform = ? " +
                    " ) " +
                    " AND pm.product_type = 1 " +
                    " AND pp. STATUS = 2 " +
                    " ORDER BY " +
                    " pm.sort_value DESC, " +
                    " pm.weight DESC, " +
                    " pm.second_priority DESC, " +
                    " pm.start_time ASC, " +
                    " pm.end_time ASC, " +
                    " pp.orderno DESC ";

            params = new Object[]{uid, uid, platform};
        }

        PageResult pr = querySql(sql, params, pagesize, pageno);


        List<JdataBean> productlist = pr.getList();
        for (JdataBean product : productlist) {
            String attrjson = product.getString("pp_attrvals");
            if (StringUtils.isNotBlank(attrjson)) {
                //将商品属性取出
                try {
                    List<ProductsAttrvalBean> jsonarr = JSON.parseArray(attrjson, ProductsAttrvalBean.class);
                    for (ProductsAttrvalBean attrbean : jsonarr) {
                        product.put(attrbean.getAttrcode(), attrbean.getValtxt());
                    }
                    product.remove("pp_attrvals");
                } catch (Exception ew) {
                    Logger.getLogger(ProductDao.class).error("JSONERROR-商品id:" + product.getString("productid"), ew);
                }
            }
        }
        return pr;

    }

    public boolean checkFlashSaleInventory(String promotionId) {
        String sql = "SELECT EXISTS (SELECT 1 from wb_promotion_flash_sale WHERE promotionid=? and amount > 0)";
        int isExist = Integer.parseInt(String.valueOf(uniqueColumnResult(sql, promotionId)));
        return isExist == 1;
    }

    public Map<String, String> findAll(String priceid, String userid) {
        StringBuilder sb = new StringBuilder("SELECT id,case promotion_type");
        for (Promotion.Type type : Promotion.Type.values()) {
            sb.append(" WHEN '").append(type.ordinal()).append("' then '").append(type.name()).append("'");
        }
        StringBuilder column = new StringBuilder("SELECT pm.id,pm.promotion_type,pm.weight,pm.second_priority FROM wb_promotions pm LEFT JOIN wb_promotion_product pmp ON pm.product_val = pmp.promotionid " +
                "LEFT JOIN (SELECT pc.id AS pc_id FROM wb_products_and_class pac INNER JOIN wb_products_class pc ON (pc.`code` = pac.classcode OR pac.classcode LIKE CONCAT(pc.`code`, '.%'))WHERE pac.productid = ?) ppc ON pm.product_val = ppc.pc_id");
        StringBuilder fragment = new StringBuilder().append(" AND platform in (").append(Promotion.Platform.COMMON.ordinal()).append(",").append(Promotion.Platform.PC.ordinal()).append(") and start_time < ? and end_time > ? and state=1 and "
                + " (product_type=0 or (product_type=1 and product_val=?)"
                + " or (product_type=2 AND pmp.productid = ?)"
                + " or (product_type=3 AND ppc.pc_id IS NOT NULL))");
        StringBuilder where = new StringBuilder("and ((wpc.type = 1 AND wpc.val = u_UserGroup)"
                + " or (wpc.type = 2 AND wpc.val = u_pricegrade)"
                + " or (wpc.type = 3 AND (IFNULL(u_province, '')!=''" +
                " and wat.province LIKE CONCAT(u_province, '%')" +
                " AND IFNULL(wat.city, '') = ''" +
                " AND IFNULL(wat.area, '') = '')" +
                " or (IFNULL(u_province, '')!=''" +
                " and wat.province LIKE CONCAT(u_province, '%')" +
                " AND IFNULL(u_city, '') != ''" +
                " AND wat.city LIKE CONCAT(u_city, '%')" +
                " AND IFNULL(wat.area, '') = '')" +
                " OR (IFNULL(u_province, '')!=''" +
                " and wat.province LIKE CONCAT(u_province, '%')" +
                " AND IFNULL(u_city, '') != ''" +
                " AND wat.city LIKE CONCAT(u_city, '%')" +
                " AND IFNULL(u_region, '') != ''" +
                " and wat.area LIKE CONCAT(u_region,'%')))))t GROUP BY weight ORDER BY weight desc,second_priority DESC");

        String now = DateConvert.toDefaultString(new Date());
        List<Map> list = mapList(sb.append(" END type FROM(").append(column).append(" WHERE pm.is_control = 0 ").append(fragment).append(" UNION ALL ").append(column).append(" JOIN wb_promotion_controlsale wpc ON pm.id = wpc.promotion_id INNER JOIN(SELECT uu.ID AS u_id,uu.UserGroup AS u_UserGroup,uu.pricegrade AS u_pricegrade,IFNULL(wc.province, '') AS u_province,IFNULL(wc.city, '') AS u_city,IFNULL(wc.region, '') AS u_region FROM wbuser uu LEFT JOIN wb_company wc ON uu.ID = wc.userid WHERE uu.ID = ?) wu"
                + " LEFT JOIN wb_area_trad wat ON (wpc.type = 3 AND wpc.val = wat.id) WHERE pm.is_control = 1 ").append(fragment).append(where).toString(), new Object[]{priceid, now, now, priceid, priceid, priceid, userid, now, now, priceid, priceid});

        Map<String, String> result = new LinkedHashMap<>();
        for (Map map : list) {
            result.put(String.valueOf(map.get("type")), String.valueOf(map.get("id")));
        }
        return result;
    }

    public List<JdataBean> findByIds(String[] promotionIdList) {
        if (promotionIdList != null) {
            String promotionIds = StringUtils.join(promotionIdList, ",");
            String sql = "SELECT " +
                    " id, " +
                    " promotion_type, " +
                    " platform, " +
                    " start_time, " +
                    " end_time, " +
                    " state, " +
                    " product_type, " +
                    " product_val, " +
                    " `name`, " +
                    " weight, " +
                    " second_priority, " +
                    " time, " +
                    " is_control " +
                    " FROM " +
                    " wb_promotions pp " +
                    " WHERE " +
                    " pp.id IN ( " + promotionIds + " ) " +
                    " AND pp.state = 1 " +
                    " AND pp.start_time <= NOW() " +
                    " AND pp.end_time > NOW() ";

            return super.querySql(sql, null);
        }
        return null;
    }

    public boolean updateFlashSaleInventory(Integer fsid, int buynum) {
        if (fsid != null && buynum != 0) {
            String sql = "UPDATE wb_promotion_flash_sale SET amount = (amount - ?), sold_amount = (sold_amount + ?) WHERE amount >= ? AND id = ?";
            int flag = super.execute(sql, new Object[]{buynum, buynum, buynum, fsid});
            return flag > 0;
        }
        return false;
    }

    public List<ActiveAreaProduct> findOnSalePromotions(int promotionType, int platform, int weight, int priority, String userid) {
        StringBuilder sb = new StringBuilder("SELECT (UNIX_TIMESTAMP(IFNULL(wp.end_time, NOW())) - UNIX_TIMESTAMP(NOW()))surplusSeconds,wp.time,wp.district_description description,wps.`name`,wps.unitname,pbs.buy_number standardNumber,pbs.send_number sendNumber,pfs.count_down countDown,if(pfs.amount<=(IFNULL(wpp.amount,0)-IFNULL(wpp.djamount,0)),pfs.amount,IFNULL(wpp.amount,0)-IFNULL(wpp.djamount,0)) amount,pfs.sold_amount soldAmount,ifnull(ap.viewpath,aps.viewpath) photo,wps.id,ppg.min_money minMoney,ppg.min_number minNumber,GROUP_CONCAT(DISTINCT CASE attrid WHEN '8' THEN val ELSE NULL END) guige,GROUP_CONCAT(DISTINCT CASE attrid WHEN '11' THEN val ELSE NULL END) changjia");
        StringBuilder fragment = new StringBuilder(" from wb_promotions wp" +
                " LEFT JOIN wb_promotion_flash_sale pfs on wp.id=pfs.promotionid" +
                " LEFT JOIN wb_promotion_buy_send pbs on wp.id=pbs.promotionid" +
                " LEFT JOIN ( select * from ( select * from wb_promotion_purchase_gift order by promotionid ASC , min_money asc , min_number asc ) ppg1 group by ppg1.promotionid ) ppg on wp.id=ppg.promotionid" +
                " join wb_products wps on wp.product_val=wps.id" +
                " join wb_products_prices wpp on wp.product_val=wpp.productid" +
                " left join app_pictures ap on ap.id=wpp.photo" +
                " left join app_pictures aps on aps.id=wps.photo" +
                " JOIN wb_products_attrval wpa ON wp.product_val = wpa.productid");
        StringBuilder where = new StringBuilder(" WHERE wps.status=2 and wp.promotion_type=? and wp.platform in(").append(Promotion.Platform.COMMON.ordinal()).append(",").append(platform).append(") and wp.start_time<? and wp.end_time>? and wp.state=1 and wp.product_type=? and wp.weight=? and wp.second_priority=? GROUP BY wps.id"
                + " ORDER BY wp.sort_value DESC");
        String sql = "";
        Object[] parms = null;
        String now = DateConvert.toDefaultString(new Date());
        if (userid == null) {
            sql = sb.append(fragment).append(where).toString();
            parms = new Object[]{promotionType, now, now, Promotion.PruductType.SINGLE.ordinal(), weight, priority};
        } else {
            sql = sb.append(",if(wp.time is not null,wp.time-ifnull(wph.times,0),null)times,if(wp.is_control=0 or pcs.promotion_id is not null,true,false)canPromotion,if(wps.iscontrol=1 and wpcs.productid is null,true,false)iscontrol,pfs.price fsPrice,if(wu.price is not null,wu.price,wpp.price)price").append(fragment).append(" left join(SELECT sum(times) times,promotionid from wb_promotion_histories WHERE userid=? GROUP BY promotionid)wph on wp.id=wph.promotionid LEFT JOIN (select pp.productid,pp.price from wbuser u join wb_products_pricegrade pp on u.pricegrade=pp.grade WHERE u.id=?)wu on wp.product_val=wu.productid" +
                    " LEFT JOIN (SELECT promotion_id from wb_promotion_controlsale wpc " +
                    "JOIN (SELECT wu.usergroup,wu.pricegrade,wc.province u_province,wc.city u_city,wc.region u_region from wb_company wc join wbuser wu on wc.userid=wu.id WHERE wu.id=? and wc.state=2)wcu" +
                    " LEFT JOIN wb_area_trad wat on (wpc.type=3 and wpc.val=wat.id)" +
                    "where (wpc.type=1 and wpc.val=usergroup) or (wpc.type=2 and wpc.val=pricegrade) or (wpc.type=3 and ( " +
                    "                   IFNULL(u_province,'') != '' " +
                    "                   AND wat.province LIKE CONCAT(u_province, '%') " +
                    "                       AND IFNULL(wat.city, '') = '' " +
                    "                      AND IFNULL(wat.area, '') = '' " +
                    "                     )" +
                    "                   OR ( " +
                    "                       IFNULL(u_province,'') != '' " +
                    "                    AND wat.province LIKE CONCAT(u_province, '%') " +
                    "                         AND IFNULL(u_city,'') != '' " +
                    "                     AND wat.city LIKE CONCAT(u_city, '%') " +
                    "                     AND IFNULL(wat.area, '') = ''" +
                    "                      ) " +
                    "                     OR ( " +
                    "                      IFNULL(u_province,'') != '' " +
                    "                      AND wat.province LIKE CONCAT(u_province, '%') " +
                    "                         AND IFNULL(u_city,'') != '' " +
                    "                     AND wat.city LIKE CONCAT(u_city, '%') " +
                    "                       AND IFNULL(u_region,'') != '' " +
                    "                       AND wat.area LIKE CONCAT(u_region, '%') " +
                    "                       ) " +
                    "                      )" +
                    ") pcs on wp.is_control=1 and wp.id=pcs.promotion_id LEFT JOIN (SELECT wpc.productid from wb_products_controlsales wpc " +
                    "JOIN (SELECT wu.usergroup,wu.pricegrade,wc.province u_province,wc.city u_city,wc.region u_region from wb_company wc join wbuser wu on wc.userid=wu.id WHERE wu.id=? and wc.state=2)wcu" +
                    " LEFT JOIN wb_area_trad wat on (wpc.type=3 and wpc.val=wat.id)" +
                    "where (wpc.type=1 and wpc.val=usergroup) or (wpc.type=2 and wpc.val=pricegrade) or (wpc.type=3 and ( " +
                    "                   IFNULL(u_province,'') != '' " +
                    "                   AND wat.province LIKE CONCAT(u_province, '%') " +
                    "                       AND IFNULL(wat.city, '') = '' " +
                    "                      AND IFNULL(wat.area, '') = '' " +
                    "                     )" +
                    "                   OR ( " +
                    "                       IFNULL(u_province,'') != '' " +
                    "                    AND wat.province LIKE CONCAT(u_province, '%') " +
                    "                         AND IFNULL(u_city,'') != '' " +
                    "                     AND wat.city LIKE CONCAT(u_city, '%') " +
                    "                     AND IFNULL(wat.area, '') = ''" +
                    "                      ) " +
                    "                     OR ( " +
                    "                      IFNULL(u_province,'') != '' " +
                    "                      AND wat.province LIKE CONCAT(u_province, '%') " +
                    "                         AND IFNULL(u_city,'') != '' " +
                    "                     AND wat.city LIKE CONCAT(u_city, '%') " +
                    "                       AND IFNULL(u_region,'') != '' " +
                    "                       AND wat.area LIKE CONCAT(u_region, '%') " +
                    "                       ) " +
                    "                      )" +
                    ") wpcs ON wp.product_val = wpcs.productid").append(where).toString();
            parms = new Object[]{userid, userid, userid, userid, promotionType, now, now, Promotion.PruductType.SINGLE.ordinal(), weight, priority};
        }

        SQLQuery query = createBeanResultSQLQuery(sql, parms, ActiveAreaProduct.class);
        query.addScalar("id");
        query.addScalar("photo");
        query.addScalar("description");
        query.addScalar("name");
        query.addScalar("unitname");
        query.addScalar("guige");
        query.addScalar("changjia");
        query.addScalar("amount", StandardBasicTypes.INTEGER);
        query.addScalar("soldAmount");
        query.addScalar("countDown", StandardBasicTypes.INTEGER);
        query.addScalar("minMoney");
        query.addScalar("minNumber");
        query.addScalar("standardNumber");
        query.addScalar("sendNumber");
        query.addScalar("surplusSeconds", StandardBasicTypes.LONG);
        query.addScalar("time");
        if (userid != null) {
            query.addScalar("fsPrice");
            query.addScalar("price");
            query.addScalar("canPromotion");
            query.addScalar("iscontrol");
            query.addScalar("times", StandardBasicTypes.INTEGER);
        }
        return getObject(query.list());
    }

    public List<JdataBean> findPromotionsByProducts(String[] pids, String userid, int platform) {
        List<JdataBean> result = new ArrayList<>();
        if (pids != null && pids.length > 0 && StringUtils.isNotEmpty(userid)) {
            String pidsStr = StringUtils.join(pids, ",");

            String sql = "SELECT " +
                    " id, " +
                    " GROUP_CONCAT(pppp.pm_type) AS pms, " +
                    " pppp.fs_price, " +
                    " pppp.fs_amount " +
                    "FROM " +
                    " ( " +
                    "  SELECT " +
                    "   pp.*, pm.id AS pm_id, " +
                    "   pm.promotion_type AS pm_type, " +
                    "   pfs.price AS fs_price, " +
                    "   pfs.amount AS fs_amount " +
                    "  FROM " +
                    "   ( " +
                    "    SELECT " +
                    "     * " +
                    "    FROM " +
                    "     wb_products " +
                    "    WHERE " +
                    "     ID IN ( " + pidsStr +
                    "     ) " +
                    "   ) pp " +
                    "  LEFT JOIN wb_promotion_product pmp ON pmp.productid = pp.id " +
                    "  LEFT JOIN wb_products_and_class ppac ON pp.id = ppac.productid " +
                    "  LEFT JOIN ( " +
                    "   SELECT " +
                    "    pc.*, pac.id AS pac_id, " +
                    "    pac.productid, " +
                    "    pac.classid, " +
                    "    pac.classcode " +
                    "   FROM " +
                    "    wb_products_class pc " +
                    "   RIGHT JOIN ( " +
                    "    SELECT " +
                    "     * " +
                    "    FROM " +
                    "     wb_products_and_class " +
                    "    WHERE " +
                    "     productid IN ( " + pidsStr +
                    "     ) " +
                    "   ) pac ON ( " +
                    "    pc.id = pac.classid " +
                    "    OR pac.classcode LIKE CONCAT(pc.`code`, '.%') " +
                    "   ) " +
                    "  ) ptc ON ptc.pac_id = ppac.id " +
                    "  LEFT JOIN ( " +
                    "   SELECT " +
                    "    prom.* " +
                    "   FROM " +
                    "    wb_promotions prom " +
                    "   LEFT JOIN ( " +
                    "    SELECT " +
                    "     wpc.id AS pcs_id, " +
                    "     wpc.promotion_id AS pcs_promotion_id " +
                    "    FROM " +
                    "     wb_promotion_controlsale wpc " +
                    "    INNER JOIN ( " +
                    "     SELECT " +
                    "      uu.ID AS u_id, " +
                    "      uu.UserGroup AS u_UserGroup, " +
                    "      uu.pricegrade AS u_pricegrade, " +
                    "      IFNULL(wc.province, '') AS u_province, " +
                    "      IFNULL(wc.city, '') AS u_city, " +
                    "      IFNULL(wc.region, '') AS u_region " +
                    "     FROM " +
                    "      wbuser uu " +
                    "     LEFT JOIN wb_company wc ON uu.ID = wc.userid " +
                    "     WHERE " +
                    "      uu.ID = ? " +
                    "    ) wu " +
                    "    LEFT JOIN wb_area_trad wat ON ( " +
                    "     wpc.type = 3 " +
                    "     AND wpc.val = wat.id " +
                    "    ) " +
                    "    WHERE " +
                    "     (type = 1 AND val = u_UserGroup) " +
                    "    OR (type = 2 AND val = u_pricegrade) " +
                    "    OR ( " +
                    "     type = 3 " +
                    "     AND ( " +
                    "      ( " +
                    "       IFNULL(u_province, '') != '' " +
                    "       AND wat.province LIKE CONCAT(u_province, '%') " +
                    "       AND IFNULL(wat.city, '') = '' " +
                    "       AND IFNULL(wat.area, '') = '' " +
                    "      ) " +
                    "      OR ( " +
                    "       IFNULL(u_province, '') != '' " +
                    "       AND wat.province LIKE CONCAT(u_province, '%') " +
                    "       AND IFNULL(u_city, '') != '' " +
                    "       AND wat.city LIKE CONCAT(u_city, '%') " +
                    "       AND IFNULL(wat.area, '') = '' " +
                    "      ) " +
                    "      OR ( " +
                    "       IFNULL(u_province, '') != '' " +
                    "       AND wat.province LIKE CONCAT(u_province, '%') " +
                    "       AND IFNULL(u_city, '') != '' " +
                    "       AND wat.city LIKE CONCAT(u_city, '%') " +
                    "       AND IFNULL(u_region, '') != '' " +
                    "       AND wat.area LIKE CONCAT(u_region, '%') " +
                    "      ) " +
                    "     ) " +
                    "    ) " +
                    "    GROUP BY " +
                    "     wpc.promotion_id " +
                    "   ) pcs ON prom.is_control = 1 " +
                    "   AND pcs.pcs_promotion_id = prom.id " +
                    "   WHERE " +
                    "    ( " +
                    "     prom.platform = 0 " +
                    "     OR prom.platform = ? " +
                    "    ) " +
                    "   AND prom.state = 1 " +
                    "   AND prom.start_time <= NOW() " +
                    "   AND prom.end_time > NOW() " +
                    "   AND ( " +
                    "    prom.is_control = 0 " +
                    "    OR pcs.pcs_id IS NOT NULL " +
                    "   ) " +
                    "  ) pm ON ( " +
                    "   pm.product_type = 0 " +
                    "   AND pm.promotion_type != 0 " +
                    "   AND pm.promotion_type != 1 " +
                    "  ) " +
                    "  OR ( " +
                    "   pm.product_type = 1 " +
                    "   AND pm.product_val = pp.id " +
                    "  ) " +
                    "  OR ( " +
                    "   pm.product_type = 2 " +
                    "   AND pm.id = pmp.promotionid " +
                    "  ) " +
                    "  OR ( " +
                    "   pm.product_type = 3 " +
                    "   AND pm.product_val = ptc.id " +
                    "  ) " +
                    "  LEFT JOIN wb_promotion_flash_sale pfs ON pm.promotion_type = 0 " +
                    "  AND pm.id = pfs.promotionid " +
                    "  GROUP BY " +
                    "   pp.id, " +
                    "   pm.promotion_type " +
                    "  ORDER BY " +
                    "   pp.id " +
                    " ) pppp " +
                    "GROUP BY " +
                    " pppp.id";
            return super.querySql(sql, new Object[]{userid, platform});
        }
        return result;
    }

    public Promotion findFlashSale(String userid, String promotionid) {
        String sql = "SELECT UNIX_TIMESTAMP(prom.end_time) - UNIX_TIMESTAMP(NOW())surplusSeconds,pfs.count_down countDown," +
                " pfs.amount, " +
                " pfs.price, " +
                "IF ( " +
                " prom.time IS NOT NULL, " +
                " time - SUM(IFNULL(wph.times, 0)), " +
                " NULL " +
                ") times, prom.time " +
                "FROM " +
                " wb_promotion_flash_sale pfs " +
                "JOIN wb_promotions prom ON pfs.promotionid = prom.id " +
                "LEFT JOIN wb_promotion_histories wph ON ( " +
                " pfs.promotionid = wph.promotionid " +
                " AND wph.userid = ? " +
                ") " +
                "WHERE " +
                " pfs.promotionid = ? ";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{userid, promotionid}, FlashSalePromotion.class);
        query.addScalar("surplusSeconds", StandardBasicTypes.LONG);
        query.addScalar("countDown");
        query.addScalar("amount");
        query.addScalar("price");
        query.addScalar("time", StandardBasicTypes.INTEGER);
        query.addScalar("times", StandardBasicTypes.INTEGER);
        return (Promotion) query.uniqueResult();
    }

    public Map<String, String> findAll(String productid, String userid, int platform) {
        String sql = "SELECT " +
                " prom.id, " +
                " CASE prom.promotion_type " +
                "WHEN '0' THEN " +
                " 'FLASH_SALE' " +
                "WHEN '1' THEN " +
                " 'BUY_SEND' " +
                "WHEN '2' THEN " +
                " 'MONEY_OFF' " +
                "WHEN '3' THEN " +
                " 'PURCHASE_GIFT' " +
                "END type " +
                "FROM " +
                " wb_promotions prom " +
                "LEFT JOIN wb_promotion_product wpp ON ( " +
                " prom.id = wpp.promotionid " +
                " AND wpp.productid = ? " +
                ") " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.id " +
                " FROM " +
                "  wb_products_and_class pac " +
                " JOIN wb_products_class wpc ON ( " +
                "  wpc.`code` = pac.classcode " +
                "  OR pac.classcode LIKE CONCAT(wpc.`code`, '.%') " +
                " ) " +
                " AND pac.productid = ? " +
                ") ppc ON prom.product_val = ppc.id " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.promotion_id " +
                " FROM " +
                "  wb_promotion_controlsale wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   wc.province, " +
                "   wc.city, " +
                "   wc.region " +
                "  FROM " +
                "   wb_company wc " +
                "  JOIN wbuser wu ON wc.userid = wu.id " +
                "  WHERE " +
                "   wu.id = ? " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                ") wpc ON prom.id = wpc.promotion_id " +
                "WHERE " +
                " prom.promotion_type IN (0, 1, 2, 3) " +
                "AND ( " +
                " prom.platform = 0 " +
                " OR prom.platform = ? " +
                ") " +
                "AND now() BETWEEN prom.start_time " +
                "AND prom.end_time " +
                "AND prom.state = 1 " +
                "AND ( " +
                " prom.product_type = 0 " +
                " OR ( " +
                "  prom.product_type = 1 " +
                "  AND prom.product_val = ? " +
                " ) " +
                " OR ( " +
                "  prom.product_type = 2 " +
                "  AND prom.product_val = wpp.promotionid " +
                " ) " +
                " OR ( " +
                "  prom.product_type = 3 " +
                "  AND prom.product_val = ppc.id " +
                " ) " +
                ") " +
                "AND ( " +
                " prom.is_control = 0 " +
                " OR ( " +
                "  prom.is_control = 1 " +
                "  AND wpc.promotion_id = prom.id " +
                " ) " +
                ") " +
                "GROUP BY " +
                " prom.weight " +
                "ORDER BY " +
                " prom.weight DESC, " +
                " prom.second_priority DESC";
        List<Map> list = mapList(sql, new Object[]{productid, productid, userid, platform, productid});

        Map<String, String> result = new LinkedHashMap<>();
        for (Map map : list) {
            result.put(String.valueOf(map.get("type")), String.valueOf(map.get("id")));
        }
        return result;
    }

    public List<JdataBean> findAvailablePromotions(String userid, Set<String> ids, int platform) {
        String pidsStr = StringUtils.join(ids, ",");

        String sql = "SELECT " +
                " id productid, " +
                " pppp.pm_id id, " +
                " CASE pppp.pm_type " +
                "WHEN '0' THEN " +
                " 'COMPLEX' " +
                "WHEN '1' THEN " +
                " 'COMPLEX' " +
                "WHEN '2' THEN " +
                " 'MONEY_OFF' " +
                "WHEN '3' THEN " +
                " 'PURCHASE_GIFT' else 'COMMON' " +
                "END type, " +
                " pppp.fs_price, " +
                " pppp.fs_amount " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "   pp.*, pm.id AS pm_id, " +
                "   pm.promotion_type AS pm_type, " +
                "   pfs.price AS fs_price, " +
                "   pfs.amount AS fs_amount " +
                "  FROM " +
                "   ( " +
                "    SELECT " +
                "     * " +
                "    FROM " +
                "     wb_products " +
                "    WHERE " +
                "     ID IN ( " + pidsStr +
                "     ) " +
                "   ) pp " +
                "  LEFT JOIN wb_promotion_product pmp ON pmp.productid = pp.id " +
                "  LEFT JOIN wb_products_and_class ppac ON pp.id = ppac.productid " +
                "  LEFT JOIN ( " +
                "   SELECT " +
                "    pc.*, pac.id AS pac_id, " +
                "    pac.productid, " +
                "    pac.classid, " +
                "    pac.classcode " +
                "   FROM " +
                "    wb_products_class pc " +
                "   RIGHT JOIN ( " +
                "    SELECT " +
                "     * " +
                "    FROM " +
                "     wb_products_and_class " +
                "    WHERE " +
                "     productid IN ( " + pidsStr +
                "     ) " +
                "   ) pac ON ( " +
                "    pc.id = pac.classid " +
                "    OR pac.classcode LIKE CONCAT(pc.`code`, '.%') " +
                "   ) " +
                "  ) ptc ON ptc.pac_id = ppac.id " +
                "  LEFT JOIN ( " +
                "   SELECT " +
                "    prom.* " +
                "   FROM " +
                "    wb_promotions prom " +
                "   LEFT JOIN ( " +
                "    SELECT " +
                "     wpc.id AS pcs_id, " +
                "     wpc.promotion_id AS pcs_promotion_id " +
                "    FROM " +
                "     wb_promotion_controlsale wpc " +
                "    INNER JOIN ( " +
                "     SELECT " +
                "      uu.ID AS u_id, " +
                "      uu.UserGroup AS u_UserGroup, " +
                "      uu.pricegrade AS u_pricegrade, " +
                "      IFNULL(wc.province, '') AS u_province, " +
                "      IFNULL(wc.city, '') AS u_city, " +
                "      IFNULL(wc.region, '') AS u_region " +
                "     FROM " +
                "      wbuser uu " +
                "     LEFT JOIN wb_company wc ON uu.ID = wc.userid " +
                "     WHERE " +
                "      uu.ID = ? " +
                "    ) wu " +
                "    LEFT JOIN wb_area_trad wat ON ( " +
                "     wpc.type = 3 " +
                "     AND wpc.val = wat.id " +
                "    ) " +
                "    WHERE " +
                "     (type = 1 AND val = u_UserGroup) " +
                "    OR (type = 2 AND val = u_pricegrade) " +
                "    OR ( " +
                "     type = 3 " +
                "     AND ( " +
                "      ( " +
                "       IFNULL(u_province, '') != '' " +
                "       AND wat.province LIKE CONCAT(u_province, '%') " +
                "       AND IFNULL(wat.city, '') = '' " +
                "       AND IFNULL(wat.area, '') = '' " +
                "      ) " +
                "      OR ( " +
                "       IFNULL(u_province, '') != '' " +
                "       AND wat.province LIKE CONCAT(u_province, '%') " +
                "       AND IFNULL(u_city, '') != '' " +
                "       AND wat.city LIKE CONCAT(u_city, '%') " +
                "       AND IFNULL(wat.area, '') = '' " +
                "      ) " +
                "      OR ( " +
                "       IFNULL(u_province, '') != '' " +
                "       AND wat.province LIKE CONCAT(u_province, '%') " +
                "       AND IFNULL(u_city, '') != '' " +
                "       AND wat.city LIKE CONCAT(u_city, '%') " +
                "       AND IFNULL(u_region, '') != '' " +
                "       AND wat.area LIKE CONCAT(u_region, '%') " +
                "      ) " +
                "     ) " +
                "    ) " +
                "    GROUP BY " +
                "     wpc.promotion_id " +
                "   ) pcs ON prom.is_control = 1 " +
                "   AND pcs.pcs_promotion_id = prom.id " +
                "   WHERE " +
                "    ( " +
                "     prom.platform = 0 " +
                "     OR prom.platform = ? " +
                "    ) " +
                "   AND prom.state = 1 " +
                "   AND prom.start_time <= NOW() " +
                "   AND prom.end_time > NOW() " +
                "   AND ( " +
                "    prom.is_control = 0 " +
                "    OR pcs.pcs_id IS NOT NULL " +
                "   ) " +
                "  ) pm ON ( " +
                "   pm.product_type = 0 " +
                "   AND pm.promotion_type != 0 " +
                "   AND pm.promotion_type != 1 " +
                "  ) " +
                "  OR ( " +
                "   pm.product_type = 1 " +
                "   AND pm.product_val = pp.id " +
                "  ) " +
                "  OR ( " +
                "   pm.product_type = 2 " +
                "   AND pm.id = pmp.promotionid " +
                "  ) " +
                "  OR ( " +
                "   pm.product_type = 3 " +
                "   AND pm.product_val = ptc.classid " +
                "  ) " +
                "  LEFT JOIN wb_promotion_flash_sale pfs ON pm.promotion_type = 0 " +
                "  AND pm.id = pfs.promotionid " +
                "  GROUP BY " +
                "   pp.id, " +
                "   pm.promotion_type " +
                "  ORDER BY " +
                "   pm.weight DESC , pm.second_priority DESC " +
                " ) pppp " +
                "GROUP BY " +
                " pppp.id";
        return super.querySql(sql, new Object[]{userid, platform});
       /* String sql = "SELECT " +
                " prom.id, " +
                " CASE prom.promotion_type WHEN 0 THEN 'COMPLEX' WHEN 1 THEN 'COMPLEX' WHEN 2 THEN 'MONEY_OFF' WHEN 3 THEN 'PURCHASE_GIFT' end type, " +
                " CONCAT( " +
                "  IFNULL(prod.id, ''), " +
                "  IFNULL(wpp.productid, ''), " +
                "  IFNULL(pac.productid, '') " +
                " ) productid FROM " +
                " wb_promotions prom " +
                "LEFT JOIN wb_promotion_flash_sale pfs on prom.id=pfs.promotionid " +
                "LEFT JOIN wb_products prod ON ( " +
                " prom.product_val = prod.id " +
                " AND prod.id IN ( " + sqlpr +
                " ) " +
                ") " +
                "LEFT JOIN wb_promotion_product wpp ON ( " +
                " prom.id = wpp.promotionid " +
                " AND wpp.productid IN ( " + sqlpr +
                " ) " +
                ") " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  pc.id, " +
                "  pac.productid " +
                " FROM " +
                "  wb_products_and_class pac " +
                " JOIN wb_products_class pc ON ( " +
                "  pac.classcode = pc.`code` " +
                "  OR pac.classcode LIKE CONCAT(pc. CODE, '.%') " +
                " ) " +
                " WHERE " +
                "  pac.productid IN ( " + sqlpr +
                "  ) " +
                ") pac ON prom.product_val = pac.id " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  SUM(IFNULL(wph.times, 0)) time, " +
                "  wph.promotionid " +
                " FROM " +
                "  wb_promotion_histories wph " +
                " WHERE " +
                "  wph.userid = ? " +
                " GROUP BY " +
                "  promotionid " +
                ") wph ON prom.id = wph.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.promotion_id, " +
                "  wpc.id " +
                " FROM " +
                "  wb_promotion_controlsale wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   wc.province, " +
                "   wc.city, " +
                "   wc.region " +
                "  FROM " +
                "   wb_company wc " +
                "  JOIN wbuser wu ON wc.userid = wu.id " +
                "  WHERE " +
                "   wc.state = 2 " +
                "  AND wu.state = 1 " +
                "  AND wu.authstate = 2 " +
                "  AND wu.id = ? " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                " GROUP BY " +
                "  wpc.promotion_id " +
                ") wpc ON prom.id = wpc.promotion_id " +
                "WHERE " +
                "(prom.promotion_type in (1,2,3) or (prom.promotion_type=0 and pfs.amount > 0)) and " +
                " (prom.platform =0 or prom.platform=" + platform + ") " +
                "AND NOW() BETWEEN prom.start_time " +
                "AND prom.end_time " +
                "AND prom.state = 1 " +
                "AND ( " +
                " prom.is_control = 0 " +
                " OR ( " +
                "  prom.is_control = 1 " +
                "  AND wpc.id IS NOT NULL " +
                " ) " +
                ") " +
                "AND ( " +
                " prom.time IS NULL " +
                " OR prom.time > IFNULL(wph.time, 0) " +
                ") " +
                "AND ( " +
                " prom.product_type = 0 " +
                " OR ( " +
                "  prom.product_type = 1 " +
                "  AND prom.product_val = prod.id " +
                " ) " +
                " OR ( " +
                "  prom.product_type = 2 " +
                "  AND prom.product_val = wpp.promotionid " +
                " ) " +
                " OR ( " +
                "  prom.product_type = 3 " +
                "  AND prom.product_val = pac.id " +
                " ) " +
                ") " +
                "GROUP BY " +
                " productid " +
                "ORDER BY " +
                " prom.weight DESC, " +
                " prom.second_priority DESC";
        return list(sql, new Object[]{userid, userid});*/
    }

    public List<FlashSalePromotion> findFlashSale(String userid, Set<String> ids, int platform) {
        String sqlpr = StringUtils.join(ids, ",");
        String sql = "SELECT prom.time, " +
                " prom.id, " +
                " prom.product_val productid, " +
                "pfs.price, " +
                "if(pfs.amount>(IFNULL(wpp.amount,0)-IFNULL(wpp.djamount,0)),(IFNULL(wpp.amount,0)-IFNULL(wpp.djamount,0)),pfs.amount)amount " +
                "FROM " +
                " wb_promotions prom " +
                "JOIN wb_promotion_flash_sale pfs ON prom.id = pfs.promotionid " +
                "JOIN wb_products_prices wpp on prom.product_val=wpp.productid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  SUM(IFNULL(wph.times, 0)) time, " +
                "  wph.promotionid " +
                " FROM " +
                "  wb_promotion_histories wph " +
                " WHERE " +
                "  wph.userid = ? " +
                " GROUP BY " +
                "  promotionid " +
                ") wph ON prom.id = wph.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.promotion_id, " +
                "  wpc.id " +
                " FROM " +
                "  wb_promotion_controlsale wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   wc.province, " +
                "   wc.city, " +
                "   wc.region " +
                "  FROM " +
                "   wb_company wc " +
                "  JOIN wbuser wu ON wc.userid = wu.id " +
                "  WHERE " +
                "   wc.state = 2 " +
                "  AND wu.state = 1 " +
                "  AND wu.authstate = 2 " +
                "  AND wu.id = ? " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                " GROUP BY " +
                "  wpc.promotion_id " +
                ") wpc ON prom.id = wpc.promotion_id " +
                "WHERE " +
                " pfs.amount > 0 " +
                "AND prom.promotion_type = 0 " +
                "AND (prom.platform =0 or prom.platform=" + platform + ") " +
                "AND NOW() BETWEEN prom.start_time " +
                "AND prom.end_time " +
                "AND prom.state = 1 " +
                "AND prom.product_type = 1 " +
                "AND prom.product_val IN (" + sqlpr + ") " +
                "AND ( " +
                " prom.time IS NULL " +
                " OR prom.time > IFNULL(wph.time, 0) " +
                ") " +
                "AND ( " +
                " prom.is_control = 0 " +
                " OR ( " +
                "  prom.is_control = 1 " +
                "  AND wpc.id IS NOT NULL " +
                " ) " +
                ")";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{userid, userid}, FlashSalePromotion.class);
        query.addScalar("id");
        query.addScalar("time");
        query.addScalar("productid", StandardBasicTypes.INTEGER);
        query.addScalar("price");
        query.addScalar("amount", StandardBasicTypes.INTEGER);
        return getObject(query.list());
    }

    public List<BuySendPromotion> findBuySend(String userid, Set<String> ids) {
        String sqlpr = StringUtils.join(ids, ",");
        String sql = "SELECT " +
                " prom.id,prom.product_val productid, " +
                "IF ( " +
                " prom.time IS NOT NULL, " +
                " prom.time - IFNULL(wph.time, 0), " +
                " NULL " +
                ") time, " +
                " pbs.buy_number buyNumber, " +
                " pbs.send_number sendNumber " +
                "FROM " +
                " wb_promotions prom " +
                "JOIN wb_promotion_buy_send pbs ON prom.id = pbs.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  SUM(IFNULL(wph.times, 0)) time, " +
                "  wph.promotionid " +
                " FROM " +
                "  wb_promotion_histories wph " +
                " WHERE " +
                "  wph.userid = ? " +
                " GROUP BY " +
                "  promotionid " +
                ") wph ON prom.id = wph.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.promotion_id, " +
                "  wpc.id " +
                " FROM " +
                "  wb_promotion_controlsale wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   wc.province, " +
                "   wc.city, " +
                "   wc.region " +
                "  FROM " +
                "   wb_company wc " +
                "  JOIN wbuser wu ON wc.userid = wu.id " +
                "  WHERE " +
                "   wc.state = 2 " +
                "  AND wu.state = 1 " +
                "  AND wu.authstate = 2 " +
                "  AND wu.id = ? " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                " GROUP BY " +
                "  wpc.promotion_id " +
                ") wpc ON prom.id = wpc.promotion_id " +
                "WHERE " +
                " prom.promotion_type = 1 " +
                "AND prom.platform IN (0, 1) " +
                "AND NOW() BETWEEN prom.start_time " +
                "AND prom.end_time " +
                "AND prom.state = 1 " +
                "AND prom.product_type = 1 " +
                "AND prom.product_val IN (" + sqlpr + ") " +
                "AND ( " +
                " prom.time IS NULL " +
                " OR prom.time > IFNULL(wph.time, 0) " +
                ") " +
                "AND ( " +
                " prom.is_control = 0 " +
                " OR ( " +
                "  prom.is_control = 1 " +
                "  AND wpc.id IS NOT NULL " +
                " ) " +
                ")";
        SQLQuery query = createBeanResultSQLQuery(sql, new Object[]{userid, userid}, BuySendPromotion.class);
        query.addScalar("id");
        query.addScalar("time", StandardBasicTypes.INTEGER);
        query.addScalar("buyNumber");
        query.addScalar("sendNumber");
        query.addScalar("productid");
        return getObject(query.list());
    }

    public List<JdataBean> findFlashSale(Map<String, Object> parms) {
        String sql = "SELECT " +
                " pfs.`name`, " +
                " pfs.price,pfs.amount, " +
                " prom.product_val productid, " +
                " " +
                "IF ( " +
                " prom.time IS NOT NULL, " +
                " prom.time - IFNULL(wph.time, 0), " +
                " NULL " +
                ") time " +
                "FROM " +
                " wb_promotions prom " +
                "JOIN wb_promotion_flash_sale pfs ON prom.id = pfs.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  SUM(IFNULL(wph.times, 0)) time, " +
                "  wph.promotionid " +
                " FROM " +
                "  wb_promotion_histories wph " +
                " WHERE " +
                "  wph.userid = :userid " +
                " GROUP BY " +
                "  wph.promotionid " +
                ") wph ON prom.id = wph.promotionid " +
                "LEFT JOIN ( " +
                " SELECT " +
                "  wpc.promotion_id " +
                " FROM " +
                "  wb_promotion_controlsale wpc " +
                " JOIN ( " +
                "  SELECT " +
                "   wu.pricegrade u_pricegrade, " +
                "   wu.usergroup u_usergroup, " +
                "   wc.province, " +
                "   wc.city, " +
                "   wc.region " +
                "  FROM " +
                "   wb_company wc " +
                "  JOIN wbuser wu ON wc.userid = wu.id " +
                "  WHERE " +
                "   wu.id = :userid " +
                " ) wuc " +
                " LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                " WHERE " +
                "  ( " +
                "   wpc.type = 1 " +
                "   AND wpc.val = u_usergroup " +
                "  ) " +
                " OR ( " +
                "  wpc.type = 2 " +
                "  AND wpc.val = u_pricegrade " +
                " ) " +
                " OR ( " +
                "  wpc.type = 3 " +
                "  AND ( " +
                "   ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wat.city, '') = '' " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wat.area, '') = '' " +
                "   ) " +
                "   OR ( " +
                "    IFNULL(wuc.province, '') != '' " +
                "    AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "    AND IFNULL(wuc.city, '') != '' " +
                "    AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "    AND IFNULL(wuc.region, '') != '' " +
                "    AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "   ) " +
                "  ) " +
                " ) " +
                ") pct ON prom.id = pct.promotion_id " +
                "WHERE " +
                " pfs.amount > 0 " +
                "AND prom.promotion_type = 0 " +
                "AND ( " +
                " prom.platform = 0 " +
                " OR prom.platform = :platform " +
                ") " +
                "AND ( " +
                " NOW() BETWEEN prom.start_time " +
                " AND prom.end_time " +
                ") " +
                "AND prom.state = 1 " +
                "AND prom.product_type = 1 " +
                "AND prom.product_val IN (:ids) " +
                "AND ( " +
                " prom.is_control = 0 " +
                " OR ( " +
                "  prom.is_control = 1 " +
                "  AND prom.id = pct.promotion_id " +
                " ) " +
                ")";
        return list(sql, parms);
    }

    public List<JdataBean> findSpecificPromotions(Map<String, Object> parms) {
        String sql = "SELECT " +
                " ori.id, " +
                " ori.promotionid, " +
                " CASE ori.type WHEN '2' THEN 'MONEY_OFF' WHEN '3' THEN 'PURCHASE_GIFT' END type " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "   prod.id, " +
                "   pm.id promotionid, " +
                "   pm.promotion_type type, " +
                "   pm.second_priority " +
                "  FROM " +
                "   wb_products prod " +
                "  LEFT JOIN wb_promotion_product wpp ON ( " +
                "   prod.id = wpp.productid " +
                "   AND wpp.productid IN (:ids) " +
                "  ) " +
                "  LEFT JOIN ( " +
                "   SELECT " +
                "    pc.id, " +
                "    pac.productid " +
                "   FROM " +
                "    wb_products_and_class pac " +
                "   JOIN wb_products_class pc ON ( " +
                "    pac.classcode = pc.`code` " +
                "    OR pac.classcode LIKE CONCAT(pc.`code`, '.%') " +
                "   ) " +
                "   WHERE " +
                "    pac.productid IN (:ids) " +
                "  ) pac ON prod.id = pac.productid " +
                "  JOIN ( " +
                "   SELECT " +
                "    prom.id, " +
                "    prom.promotion_type, " +
                "    prom.product_type, " +
                "    prom.product_val, " +
                "    prom.second_priority " +
                "   FROM " +
                "    wb_promotions prom " +
                "   LEFT JOIN ( " +
                "    SELECT " +
                "     SUM(IFNULL(wph.times, 0)) time, " +
                "     wph.promotionid " +
                "    FROM " +
                "     wb_promotion_histories wph " +
                "    WHERE " +
                "     wph.userid = :userid " +
                "    GROUP BY " +
                "     wph.promotionid " +
                "   ) wph ON prom.id = wph.promotionid " +
                "   LEFT JOIN ( " +
                "    SELECT " +
                "     wpc.promotion_id " +
                "    FROM " +
                "     wb_promotion_controlsale wpc " +
                "    JOIN ( " +
                "     SELECT " +
                "      wu.pricegrade u_pricegrade, " +
                "      wu.usergroup u_usergroup, " +
                "      wc.province, " +
                "      wc.city, " +
                "      wc.region " +
                "     FROM " +
                "      wb_company wc " +
                "     JOIN wbuser wu ON wc.userid = wu.id " +
                "     WHERE " +
                "      wu.id = :userid " +
                "    ) wuc " +
                "    LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                "    WHERE " +
                "     ( " +
                "      wpc.type = 1 " +
                "      AND wpc.val = u_usergroup " +
                "     ) " +
                "    OR ( " +
                "     wpc.type = 2 " +
                "     AND wpc.val = u_pricegrade " +
                "    ) " +
                "    OR ( " +
                "     wpc.type = 3 " +
                "     AND ( " +
                "      ( " +
                "       IFNULL(wuc.province, '') != '' " +
                "       AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "       AND IFNULL(wat.city, '') = '' " +
                "       AND IFNULL(wat.area, '') = '' " +
                "      ) " +
                "      OR ( " +
                "       IFNULL(wuc.province, '') != '' " +
                "       AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "       AND IFNULL(wuc.city, '') != '' " +
                "       AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "       AND IFNULL(wat.area, '') = '' " +
                "      ) " +
                "      OR ( " +
                "       IFNULL(wuc.province, '') != '' " +
                "       AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "       AND IFNULL(wuc.city, '') != '' " +
                "       AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "       AND IFNULL(wuc.region, '') != '' " +
                "       AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "      ) " +
                "     ) " +
                "    ) " +
                "   ) pct ON prom.id = pct.promotion_id " +
                "   WHERE " +
                "    prom.promotion_type IN (2, 3) " +
                "   AND ( " +
                "    prom.platform = 0 " +
                "    OR prom.platform = :platform " +
                "   ) " +
                "   AND ( " +
                "    NOW() BETWEEN prom.start_time " +
                "    AND prom.end_time " +
                "   ) " +
                "   AND prom.state = 1 " +
                "   AND ( " +
                "    prom.time IS NULL " +
                "    OR prom.time > IFNULL(wph.time, 0) " +
                "   ) " +
                "   AND ( " +
                "    prom.is_control = 0 " +
                "    OR ( " +
                "     prom.is_control = 1 " +
                "     AND prom.id = pct.promotion_id " +
                "    ) " +
                "   ) " +
                "  ) pm ON ( " +
                "   pm.product_type = 0 " +
                "   OR ( " +
                "    pm.product_type = 1 " +
                "    AND pm.product_val = prod.id " +
                "   ) " +
                "   OR ( " +
                "    pm.product_type = 2 " +
                "    AND pm.product_val = wpp.promotionid " +
                "   ) " +
                "   OR ( " +
                "    pm.product_type = 3 " +
                "    AND pm.product_val = pac.id " +
                "   ) " +
                "  ) " +
                "  WHERE " +
                "   prod.id IN (:ids) " +
                " ) ori " +
                "JOIN ( " +
                " SELECT " +
                "  prod.id, " +
                "  pm.id promotionid, " +
                "  max(pm.second_priority) second_priority " +
                " FROM " +
                "  wb_products prod " +
                " LEFT JOIN wb_promotion_product wpp ON ( " +
                "  prod.id = wpp.productid " +
                "  AND wpp.productid IN (:ids) " +
                " ) " +
                " LEFT JOIN ( " +
                "  SELECT " +
                "   pc.id, " +
                "   pac.productid " +
                "  FROM " +
                "   wb_products_and_class pac " +
                "  JOIN wb_products_class pc ON ( " +
                "   pac.classcode = pc.`code` " +
                "   OR pac.classcode LIKE CONCAT(pc.`code`, '.%') " +
                "  ) " +
                "  WHERE " +
                "   pac.productid IN (:ids) " +
                " ) pac ON prod.id = pac.productid " +
                " JOIN ( " +
                "  SELECT " +
                "   prom.id, " +
                "   prom.product_type, " +
                "   prom.product_val, " +
                "   prom.second_priority " +
                "  FROM " +
                "   wb_promotions prom " +
                "  LEFT JOIN ( " +
                "   SELECT " +
                "    SUM(IFNULL(wph.times, 0)) time, " +
                "    wph.promotionid " +
                "   FROM " +
                "    wb_promotion_histories wph " +
                "   WHERE " +
                "    wph.userid = :userid " +
                "   GROUP BY " +
                "    wph.promotionid " +
                "  ) wph ON prom.id = wph.promotionid " +
                "  LEFT JOIN ( " +
                "   SELECT " +
                "    wpc.promotion_id " +
                "   FROM " +
                "    wb_promotion_controlsale wpc " +
                "   JOIN ( " +
                "    SELECT " +
                "     wu.pricegrade u_pricegrade, " +
                "     wu.usergroup u_usergroup, " +
                "     wc.province, " +
                "     wc.city, " +
                "     wc.region " +
                "    FROM " +
                "     wb_company wc " +
                "    JOIN wbuser wu ON wc.userid = wu.id " +
                "    WHERE " +
                "     wu.id = :userid " +
                "   ) wuc " +
                "   LEFT JOIN wb_area_trad wat ON wat.id = wpc.val " +
                "   WHERE " +
                "    ( " +
                "     wpc.type = 1 " +
                "     AND wpc.val = u_usergroup " +
                "    ) " +
                "   OR ( " +
                "    wpc.type = 2 " +
                "    AND wpc.val = u_pricegrade " +
                "   ) " +
                "   OR ( " +
                "    wpc.type = 3 " +
                "    AND ( " +
                "     ( " +
                "      IFNULL(wuc.province, '') != '' " +
                "      AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "      AND IFNULL(wat.city, '') = '' " +
                "      AND IFNULL(wat.area, '') = '' " +
                "     ) " +
                "     OR ( " +
                "      IFNULL(wuc.province, '') != '' " +
                "      AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "      AND IFNULL(wuc.city, '') != '' " +
                "      AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "      AND IFNULL(wat.area, '') = '' " +
                "     ) " +
                "     OR ( " +
                "      IFNULL(wuc.province, '') != '' " +
                "      AND wat.province LIKE CONCAT(wuc.province, '%') " +
                "      AND IFNULL(wuc.city, '') != '' " +
                "      AND wat.city LIKE CONCAT(wuc.city, '%') " +
                "      AND IFNULL(wuc.region, '') != '' " +
                "      AND wat.area LIKE CONCAT(wuc.region, '%') " +
                "     ) " +
                "    ) " +
                "   ) " +
                "  ) pct ON prom.id = pct.promotion_id " +
                "  WHERE " +
                "   prom.promotion_type IN (2, 3) " +
                "  AND ( " +
                "   prom.platform = 0 " +
                "   OR prom.platform = :platform " +
                "  ) " +
                "  AND ( " +
                "   NOW() BETWEEN prom.start_time " +
                "   AND prom.end_time " +
                "  ) " +
                "  AND prom.state = 1 " +
                "  AND ( " +
                "   prom.time IS NULL " +
                "   OR prom.time > IFNULL(wph.time, 0) " +
                "  ) " +
                "  AND ( " +
                "   prom.is_control = 0 " +
                "   OR ( " +
                "    prom.is_control = 1 " +
                "    AND prom.id = pct.promotion_id " +
                "   ) " +
                "  ) " +
                " ) pm ON ( " +
                "  pm.product_type = 0 " +
                "  OR ( " +
                "   pm.product_type = 1 " +
                "   AND pm.product_val = prod.id " +
                "  ) " +
                "  OR ( " +
                "   pm.product_type = 2 " +
                "   AND pm.product_val = wpp.promotionid " +
                "  ) " +
                "  OR ( " +
                "   pm.product_type = 3 " +
                "   AND pm.product_val = pac.id " +
                "  ) " +
                " ) " +
                " WHERE " +
                "  prod.id IN (:ids) " +
                " GROUP BY " +
                "  prod.id " +
                ") pro ON ( " +
                " ori.id = pro.id " +
                " AND ori.second_priority = pro.second_priority " +
                ") " +
                "GROUP BY " +
                " ori.id";
        return list(sql, parms);
    }
}
